#!/usr/bin/env python2.5
# encoding: utf-8

# http://dev.mysql.com/downloads/python.html
# http://docs.python.org/lib/module-sqlite3.html

import sys
import os
import unittest

import sqlite3
import MySQLdb

class DBAPI2Server(object):
  engines = {
    'sqlite3': sqlite3,
    'sqlite':  sqlite3,
    'SQLite':  sqlite3,
    'MySQL':   MySQLdb,
    'mysql':   MySQLdb
  }
  
  paramstyles = {
    'qmark':  '?',
    'format': '%s'
  }
  
  def __init__(self, engineName, server):
    """
    DBAPI2Server(engineName, server)
    
      - engineName must be the name of one of the compatible engines
        (DBAPI2Server.engines)
      - Server must be a tuple containing the relevant connection information
    """
    
    if not engineName in self.engines.keys():
      raise TypeError(
        "Engine %s is of an unknown type (known engines : %s)"%(
          engineName, str(self.engines)))
    
    self.engineName = engineName
    self.engine     = self.engines[engineName]
    self.paramstyle = self.paramstyles[self.engine.paramstyle]
    
    self.connection = self.engine.connect(*server)
  
  @property
  def cursor(self):
    return self.connection.cursor()
  
  @property
  def kwAI(self):
    """keyword for autoincrement depending on the engine"""
    return 'AUTO%sINCREMENT'%('' if self.engineName == 'sqlite3' else '_')
  
  def execute(self, query, params = ()):
    cursor = self.cursor
    
    cursor.execute(
      query.replace('?', self.paramstyle),
      params)
    
    return cursor
  
  def createfunction(self, name, args, expr):
    if self.engineName == 'MySQL':
      query = '''
        CREATE FUNCTION %s(%s)
          RETURNS INT
          DETERMINISTIC
          RETURN %s'''%(
            name, ', '.join([arg + ' INT' for arg in args]), expr)
      
      self.cursor.execute(query)
    elif self.engineName == 'sqlite3':
      self.connection.create_function(
        name,
        len(args),
        eval(
        'lambda %s: %s'%(', '.join(args), expr))
        )
    else:
      raise TypeError(
        'Engine %s does not support createfunction.'%(self.engineName))
    
  
  # TODO : Code aggregation creation !
  def createaggregatefunction(self, name, startTimeIterator, endTimeIterator, valueIterator, grouper):
    raise TypeError('createaggregatefunction is not yet implemented !')
  
  def __del__(self):
    try:
      self.connection.close()
    except AttributeError:
      pass

from tempfile import mkstemp

mysqlConnectionParameters = ('localhost', 'root', '')

class DBAPI2ServerConnectionTests(unittest.TestCase):
  def setUp(self):
    self.tmpfile = mkstemp()[1]
  
  def testInvalidEngine(self):
    self.assertRaises(
      TypeError,
      DBAPI2Server, 'UnknownEngine', (self.tmpfile,))
  
  def testSQLite3Connection(self):
    foo = DBAPI2Server('sqlite3', (':memory:',))
    del foo
    
    foo = DBAPI2Server('sqlite3', (self.tmpfile,))
    del foo
  
  def testMySQLConnection(self):
    global mysqlConnectionParameters
    DBAPI2Server('MySQL', mysqlConnectionParameters)
  
  def tearDown(self):
    os.unlink(self.tmpfile)


class DBAPI2ServerWithCursorTest(unittest.TestCase):
  def setUp(self):
    self.mysqlConnection  = DBAPI2Server('MySQL', mysqlConnectionParameters)
    self.mysqlCursor      = self.mysqlConnection.cursor
    
    self.sqliteConnection = DBAPI2Server('sqlite3', (':memory:',))
    self.sqliteCursor     = self.sqliteConnection.cursor
  
  def tearDown(self):
    del self.mysqlConnection
    del self.sqliteConnection

class DBAPI2ServerQueryTests(DBAPI2ServerWithCursorTest):
  def testMySQLQuery(self):
    self.mysqlCursor.execute('CREATE DATABASE testfoobar');
    self.mysqlCursor.execute('DROP DATABASE testfoobar');
    
    self.sqliteCursor.execute('CREATE TABLE testfoobar (a int)');
    self.sqliteCursor.execute('DROP TABLE testfoobar');

class DBAPI2ServerCreateFunctionTests(DBAPI2ServerWithCursorTest):
  name2 = 'testFoo'
  args2 = ('v', 'w')
  expr2 = 'v + w'
  
  name3 = 'testFoo2'
  args3 = ('v', 'w', 'foo')
  expr3 = 'v * foo + w'
  
  def setUp(self):
    DBAPI2ServerWithCursorTest.setUp(self)
    
    self.mysqlCursor.execute('CREATE DATABASE testDBAPI2')
    self.mysqlCursor.execute('USE testDBAPI2')
  
  def testCreateFunction2Args(self):
    self.mysqlConnection.createfunction(self.name2, self.args2, self.expr2)
    
    self.sqliteConnection.createfunction(self.name2, self.args2, self.expr2)
  
  def testCreateFunction3Args(self):
    self.mysqlConnection.createfunction(self.name3, self.args3, self.expr3)
    
    self.sqliteConnection.createfunction(self.name3, self.args3, self.expr3)
  
  def tearDown(self):
    self.mysqlCursor.execute('DROP DATABASE testDBAPI2')
    
    DBAPI2ServerWithCursorTest.tearDown(self)


if __name__ == '__main__':
  unittest.main()
